package IT.DataAccess;

import IT.Entities.CatalogoEstado;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import java.util.logging.Level;
import java.util.logging.Logger;

public class DatCatalogoEstado implements IDatCatalogoEstado{
    
    private CatalogoEstado oCatalogo;
    private List<CatalogoEstado> oLista;
    private BDConexion oBase;
    
    private Connection oConexion;
    private CallableStatement oCst;    
    private ResultSet oRst;    
    
    public List<CatalogoEstado> Listar() {
        oLista=new ArrayList<CatalogoEstado>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoListar()}");
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new CatalogoEstado( oRst.getInt("cod_est_in"), 
                            oRst.getString("nom_est_vc"), oRst.getInt("val_est_in")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }

    public CatalogoEstado Ind(int nCodigo, int nValor) {
         oCatalogo=new CatalogoEstado();
         
         try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoInd(?,?)}");
            oCst.setInt(1, nCodigo);
            oCst.setInt(2, nValor);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oCatalogo.setCod_est_in(oRst.getInt("cod_est_in"));
                oCatalogo.setNom_est_vc(oRst.getString("nom_est_vc"));
                oCatalogo.setVal_est_in(oRst.getInt("val_est_in"));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oCatalogo;
    }

    public List<CatalogoEstado> Ubi(int nCodigo) {
         oLista=new ArrayList<CatalogoEstado>();
         
          try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoUbi(?)}");
            oCst.setInt(1, nCodigo);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new CatalogoEstado( oRst.getInt("cod_est_in"),
                        oRst.getString("nom_est_vc"), oRst.getInt("val_est_in")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }

    public List<CatalogoEstado> Buscar(int nCodigo) {
        oLista=new ArrayList<CatalogoEstado>();
         
          try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoBuscar(?)}");
            oCst.setInt(1, nCodigo);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new CatalogoEstado( oRst.getInt("cod_est_in"),
                        oRst.getString("nom_est_vc"), oRst.getInt("val_est_in")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }
    public int Insertar(CatalogoEstado ent) {
       int nResultado=0;
       
       try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoInsertar(?,?,? ,?)}");              
            oCst.setInt(1, ent.getCod_est_in());
            oCst.setString(2, ent.getNom_est_vc());
            oCst.setInt(3, ent.getVal_est_in());
            oCst.execute();
            nResultado=oCst.getInt(4);
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
       return nResultado=0;
    }

    public void Editar(CatalogoEstado ent) {
         try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoEditar(?,?,?)}");              
            oCst.setInt(1, ent.getCod_est_in());
            oCst.setString(2, ent.getNom_est_vc());
            oCst.setInt(3, ent.getVal_est_in());
            oCst.execute();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void Eliminar(int nCodigo, int nValor) {
    
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_CatalogoEliminar(?,?)}");              
            oCst.setInt(1, nCodigo);          
            oCst.setInt(2, nValor);
            oCst.execute();
        
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
     
    }
}
